use serde::{Deserialize, Serialize};
use crate::{error::SqlxErr, store::get_pool, utils::current_timestamp};

use super::Conn;

#[derive(Debug, Serialize)]
pub struct TbCargo {
    pub id: u64,
    device_id: u64,
    cargo_col: u8,
    cargo_row: u8,
    num: u8,
    product_id: Option<u64>,
    update_timestamp: Option<u64>,
}

#[derive(Debug, Deserialize)]
pub struct CargoInfo {
    col: u8,
    row: u8,
    product_id: u64,
}

pub async fn change_product(device_id: u64, cargos: &[CargoInfo]) -> Result<(), SqlxErr> {
    let mut tx = get_pool().begin().await?;
    for cargo in cargos {
        sqlx::query!(r#"
            UPDATE tb_cargo 
            SET product_id = ?
            WHERE device_id = ? 
            AND cargo_col = ? 
            AND cargo_row = ? 
            "#,
            cargo.product_id, device_id, cargo.col, cargo.row
        ).execute(&mut *tx).await?;
    }
    tx.commit().await?;
    Ok(())
}

#[derive(Debug, Deserialize)]
pub struct CargoTimestamp {
    col: u8,
    row: u8,
    timestamp: u64,
    num: u8,
}

pub async fn change_update_timestmp(device_id: u64, cargos: &[CargoTimestamp]) -> Result<(), SqlxErr> {
    let mut tx = get_pool().begin().await?;
    for cargo in cargos {
        sqlx::query!(r#"
            UPDATE tb_cargo
            SET update_timestamp = ?, num = ? 
            WHERE device_id = ? 
            AND cargo_col = ? 
            AND cargo_row = ?
        "#, cargo.timestamp, cargo.num, device_id, cargo.col, cargo.row)
        .execute(&mut *tx)
        .await?;
    }
    tx.commit().await?;

    Ok(())
}

pub async fn create(conn: &mut Conn, device_id: u64) -> Result<(), SqlxErr> {
    for col in 0..=1u8 {
        for row in 1..=40u8 {
            sqlx::query!(
                "INSERT INTO tb_cargo (device_id, cargo_col, cargo_row, num) VALUES (?, ?, ?, ?)",
                device_id,
                col,
                row,
                0
            )
            .execute(&mut *conn)
            .await?;
        }
    }
    Ok(())
}

pub async fn delete(conn: &mut Conn, device_id: u64) -> Result<(), SqlxErr> {
    sqlx::query!("DELETE FROM tb_cargo WHERE device_id = ?", device_id).execute(conn).await?;
    Ok(())
}

#[derive(Debug, Serialize)]
pub struct CargoExpiration {
    cargo_col: u8,
    cargo_row: u8,
    name: String,
}

// 查询过期的商品
pub async fn query_expiration(device_id: u64) -> Result<Box<[CargoExpiration]>, SqlxErr> {
    let now = current_timestamp();
    let res = sqlx::query_as!(
        CargoExpiration,
        r#"
            SELECT tb_cargo.cargo_col, tb_cargo.cargo_row, tb_product.name 
            FROM tb_cargo 
            JOIN tb_product ON tb_cargo.product_id = tb_product.id 
            WHERE device_id = ? 
            AND (tb_cargo.update_timestamp + tb_product.expiration) <= ? 
            AND tb_cargo.num != 0 
        "#,
        device_id, now
    ).fetch_all(get_pool()).await?;

    Ok(res.into_boxed_slice())
}

pub async fn get(device_id: u64) -> Result<Box<[TbCargo]>, SqlxErr> {
    let cargos = sqlx::query_as!(
        TbCargo,
        r#"
        SELECT id, device_id, cargo_col, cargo_row, num, product_id, update_timestamp 
        FROM tb_cargo 
        WHERE device_id = ?
        "#,
        device_id
    ).fetch_all(get_pool()).await?;

    Ok(cargos.into_boxed_slice())
}
